package top.went.db.dao;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import top.went.pojo.PayBackDetailEntity;
import top.went.pojo.PlanPayBackEntity;
import top.went.pojo.PlanPayDetailEntity;

import java.util.List;

/**
 * 回款记录Dao
 */
public interface PayBackDetailDao extends JpaRepository<PayBackDetailEntity,Integer> {
    /**
     * 查询所有回款计划
     * @return
     */
    public List<PayBackDetailEntity> findAllByMagicDeleteOrderByPbdId (long delete, Pageable pageable);

    /**
     * 根据回款日期查询所有付款
     * @param pbd_date
     * @return
     */
    @Query(nativeQuery = true,value = "SELECT * FROM TB_PAY_BACK_DETAIL  WHERE  to_char(PBD_DATE,'yyyy-MM-dd') like ?1 and MAGIC_DELETE=0 order by ?#{#pageable}",
            countQuery = "SELECT count(*) FROM TB_PAY_BACK_DETAIL WHERE to_char(PBD_DATE,'yyyy-MM-dd') like ?1 and magic_delete=0")
    public Page<PayBackDetailEntity> findAllByDate(String pbd_date, Pageable pageable);

    /**
     * 根据回款日期查询所有付款数量
     * @return
     */
    @Query(nativeQuery = true,value = "select count(*) from TB_PAY_BACK_DETAIL where magic_delete=0 order by pbd_id desc")
    public int pbd_findAllCount();

    /**
     * 根据回款日期查询所有付款
     * @param ppd_date
     * @return
     */

    @Query(nativeQuery = true,value = "SELECT count(*) FROM TB_PAY_BACK_DETAIL  WHERE  to_char(PBD_DATE,'yyyy-MM-dd') like ?1 and MAGIC_DELETE=0 order by PBD_ID desc")
    public int pbd_findByPpdDateCount(String ppd_date);
    /**
     * 加载
     * @param delete
     * @param pbdId
     * @return
     */
    public PayBackDetailEntity findAllByMagicDeleteAndPbdId (long delete, int pbdId);
    /**
     * 根据订单合同统计回款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select max(nvl(p.ORDER_TITLE,'其他')) TYPE, sum(nvl(o.PBD_MONEY,0)) count from TB_PAY_BACK_DETAIL o left join TB_ORDER p on o.ORDER_ID=p.ORDER_ID group by o.ORDER_ID")
    List<Object[]> pbdstatisticsOrderCategoryMoney();
    /**
     * 根据年统计回款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select nvl(to_char(o.PBD_DATE,'yyyy'),'其他') TYPE, sum(nvl(o.PBD_MONEY,0)) count from TB_PAY_BACK_DETAIL o group by to_char(o.PBD_DATE,'yyyy')")
    List<Object[]> pbdstatisticsByYear();
    /**
     * 根据月统计回款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "select nvl(to_char(o.PBD_DATE,'yyyy-MM'),'其他') TYPE, sum(nvl(o.PBD_MONEY,0)) count from TB_PAY_BACK_DETAIL o group by to_char(o.PBD_DATE,'yyyy-MM')")
    List<Object[]> pbdstatisticsByMonth();
    /**
     * 根据日期统计回款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "    select nvl(to_char(o.PBD_DATE,'yyyy-MM-dd'),'其他') TYPE, sum(nvl(o.PBD_MONEY,0)) count from TB_PAY_BACK_DETAIL o group by to_char(o.PBD_DATE,'yyyy-MM-dd')")
    List<Object[]> pbdstatisticsByDate();

    /**
     * 根据客户统计回款金额分布
     * @return
     */
    @Query(nativeQuery = true,value = "    select max(nvl(p.CUS_NAME,'其他')) TYPE, sum(nvl(o.PBD_MONEY,0)) count from TB_PAY_BACK_DETAIL o left join TB_CUSTOMER p on o.CUS_ID=p.CUS_ID group by o.CUS_ID")
    List<Object[]> pbdstatisticsByCus();
}
